package main

import (
	"container/list"
	"encoding/csv"
	"fmt"
	"github.com/tealeg/xlsx"
	"os"
	"path/filepath"
	"strconv"
	"strings"
)



var start_map = make(map[string]interface{})

var start_rows_name = list.New()

func main() {
	var start_name string
	var end_name string

	fmt.Println(".........................列名..................................................")
	fmt.Println("产品号	 产品名称	客户经理	催收情况	楼盘	客户号	客户名	贷款帐号	扣款帐号	期号	应还款日期	应收金额	应收本金额	应收利息	应收罚息	逾期天数	公司电话	住宅电话	手机	通信地址")
	fmt.Println("...........................................................................")
	fmt.Println("请注意 月末表和月初表 每列顺序必须严格按照上面的列名")
	fmt.Println("...........................................................................")
	fmt.Println("请将月初文件名放到本程序目录下，命名为:start_month.xlsx")
	fmt.Println("请将月末文件名放到本程序目录下，命名为:end_month.xlsx")
	fmt.Println("按回城开始")
	fmt.Scanf("%s",&start_name)
	start_name="start_month.xlsx"
	end_name="end_month.xlsx"


	var customers, dirt_customers = read_month(start_name)
	
	start_name=strings.Replace(start_name,".xlsx",".csv",1)
	write_month("fixed_"+start_name, customers)
	write_month("dirt_"+start_name, dirt_customers)

	fmt.Println("月初正常统计客户：",customers.Len()," 月初呆数据客户：",dirt_customers.Len())

	var end_month_cus,dirt_end_month_cus=read_month(end_name)

	end_name=strings.Replace(end_name,".xlsx",".csv",1)
	write_end_month("fixed_"+end_name, end_month_cus,customers,dirt_customers)
	write_end_month("dirt_"+end_name, dirt_end_month_cus,customers,dirt_customers)

	fmt.Println("月末正常统计客户：",end_month_cus.Len()," 月末呆数据客户：",dirt_end_month_cus.Len())

	customers=join_data(customers,end_month_cus,dirt_end_month_cus)

	write_month("final_result.csv", customers)

}

/**
*
*如果一个客户在月末被认为呆数据，那么月初也应该是呆数据
*遍历月初数据，通过找到月末数据，进行合并，并剔除该月末数据
*			   未找到，去月末呆数据中找，如果找到，直接从月初数据中剔除
*最后遍历完月初数据，将剩余的月末数据合并到月初数据中，如果新数据有两项，那么发出警告
*
*/
func join_data(customers *list.List,end_customers *list.List, dirt_customers *list.List)(*list.List){

	for one_cus := customers.Front(); one_cus != nil;  {
		cus := one_cus.Value.(*list.List)
		var id =getId(cus)

		if len(id)==0{
			fmt.Println("[警告]月初表中，客户ID 异常",one_cus)
			one_cus = one_cus.Next()
			continue
		}

		var dirt_cus=find(id,dirt_customers)

		if dirt_cus!=nil{
			var remove_one=one_cus;
			one_cus=one_cus.Next()
			fmt.Println("[警告]客户：",id," 在月末表中为呆数据，将其从月初表统计中移除")
			customers.Remove(remove_one)
			dirt_customers.Remove(dirt_cus)
			continue
		}

		var end_cus=find(id,end_customers)

		if(end_cus!=nil){
			var remove_one=one_cus;
			one_cus=one_cus.Next()
			customers.Remove(remove_one)
			end_customers.Remove(end_cus)
			continue
		}

		//cus=fullfile_start(cus)
		one_cus = one_cus.Next()
	}

	

	return customers
}

func getId(customer *list.List)(id string){


	for itr_row_one := customer.Front(); itr_row_one != nil; itr_row_one = itr_row_one.Next()  {

		itr_row_one := itr_row_one.Value.(*list.List)

		if itr_row_one.Len()<6 {									//避免出现空值
			return ""
		}

		var result= itr_row_one.Front().Next().Next().Next().Next().Next().Value.(string)		//获取客户号

		return result

	}
	return ""
}

func find(id string,customers *list.List)(dirt_cus *list.Element){

	for one_cus := customers.Front(); one_cus != nil; one_cus = one_cus.Next()  {

		rows := one_cus.Value.(*list.List)
		var did=getId(rows)
		if id==did{
			return one_cus;
		}
	}
	return nil
}


func combine(start_cus *list.List,end_cus *list.List)(result_cus *list.List){

	var total_should_pay float32 =0.0
	var total_base float32=0.0
	var total_interest float32=0.0
	var total_penalty float32=0.0
	var summary=getRowOfElement(start_cus.Len()-1,start_cus)
	start_cus.Remove(summary)

	var end_summary=getRowOfElement(end_cus.Len()-1,end_cus)
	end_cus.Remove(end_summary)

	for row := start_cus.Front(); row != nil; row = row.Next()  {

		var start_row=row.Value.(*list.List)
		var month=getFloatCell(12,start_row)				//获取地13个元素

		var end_month_row *list.List
		var index,has_end_record=month_record_exist(month,end_cus)
		if has_end_record{
		
		   end_month_row=getRow(index,end_cus)				//月末对应记录
		}
		
		var start_should_pay=getFloatCell(13,start_row)			//获取地14个元素,月初应付金额
		total_should_pay =total_should_pay+ start_should_pay
		var end_should_pay float32=0.0

		if end_month_row!=nil{
			end_should_pay=getFloatCell(13,end_month_row)		//获取地14个元素,月末应付金额
		}

		total_base =total_base+ getFloatCell(14, start_row)		//获取地15个元素
		total_interest =total_interest+ getFloatCell(15, start_row)	//获取地16个元素
		total_penalty=total_penalty+getFloatCell(16, start_row)		//获取地17个元素
		
		var has_paied=start_should_pay-end_should_pay
		if has_paied<0{
			has_paied=0
		}
		start_row.PushBack(end_should_pay)
		start_row.PushBack(has_paied)

	}

	var end_record=getRow(0,end_cus)
	var extra_row *list.List

	if end_record!=nil {
	
		var end_reocrd_month=getFloatCell(12,end_record)					//获取地13个元素
		var _,has_start_record=month_record_exist(end_reocrd_month,start_cus)
		if !has_start_record {
			extra_row=end_record
		}
	}

	if extra_row==nil{
	
		end_record=getRow(1,end_cus)
		if end_record!=nil {
			var end_reocrd_month=getFloatCell(12,end_record)				//获取地13个元素
			var _,has_start_record=month_record_exist(end_reocrd_month,start_cus)
			if !has_start_record {
				extra_row=end_record
			}
	}

	}

	if extra_row!=nil{
		var start_should_pay=getFloatCell(13,extra_row)				//获取地14个元素,月末应付金额
		total_should_pay=total_should_pay+start_should_pay
		total_base =total_base+ getFloatCell(14, extra_row)			//获取地15个元素
		total_interest =total_interest+ getFloatCell(15, extra_row)	//获取地16个元素
		total_penalty=total_penalty+getFloatCell(16, extra_row)		//获取地17个元素
		extra_row.PushBack(start_should_pay)						//结果，附加月末应付金额
		extra_row.PushBack(0)										//结果，附加月末已付金额
		start_cus.PushBack(extra_row)
	}

	var summary_list=summary.Value.(*list.List)

	putRowValue(13,total_should_pay,summary_list)				//每个客户做月末统计
	putRowValue(14,total_base,summary_list)
	putRowValue(15,total_interest,summary_list)
	putRowValue(16,total_penalty,summary_list)

	start_cus.PushBack(summary_list)

	return start_cus
}

func month_record_exist(month float32,end_cus *list.List) (index int,has bool){

	has=false
	index=0
	for row := end_cus.Front(); row != nil; row = row.Next()  {

		var r=row.Value.(*list.List)

		var em=getFloatCell(12,r)					//获取地13个元素

		if(month==em){
		
			return index,true
		}
		index++

	}



	return 0,has

}


func fullfile_start(start_cus *list.List)(result_cus *list.List){

	var total_should_pay float32 =0.0
	var total_base float32=0.0
	var total_interest float32=0.0
	var total_penalty float32=0.0

	var summary=getRowOfElement(start_cus.Len()-1,start_cus)


	start_cus.Remove(summary)


	row_num:=0
	for row := start_cus.Front(); row != nil; row = row.Next()  {

		var start_row=row.Value.(*list.List)


		var start_should_pay=getFloatCell(13,start_row)			//获取地14个元素,月初应付金额
		total_should_pay =total_should_pay+ start_should_pay
		var end_should_pay float32=0.0


		total_base =total_base+ getFloatCell(14, start_row)		//获取地15个元素
		total_interest =total_interest+ getFloatCell(15, start_row)	//获取地16个元素
		total_penalty=total_penalty+getFloatCell(16, start_row)		//获取地17个元素
		
		var has_paied=start_should_pay-end_should_pay
		if has_paied<0{
			has_paied=0
		}
		start_row.PushBack(end_should_pay)
		start_row.PushBack(has_paied)

		row_num++
	}


	var summary_list=summary.Value.(*list.List)



	putRowValue(13,total_should_pay,summary_list)
	putRowValue(14,total_base,summary_list)
	putRowValue(16,total_interest,summary_list)
	putRowValue(18,total_penalty,summary_list)

	start_cus.PushBack(summary_list)

	return start_cus
}

func putRowValue(index int,v interface{},row *list.List){

	//如果设置的值超过已有的row长度，那么将附加到row的末尾
	if row.Len()<=index{

		for  i:=0;i<index-(row.Len()-1);i++{
			if(row.Len()+i!=index){
				row.PushBack("")
			}else{
				row.PushBack(v)
			}

		}
		return
	}
	i:=0
	for cel := row.Front(); cel != nil; cel = cel.Next()  {
		if i==index{
			cel.Value=v
		}
		i++
	}

}

func getRow(index int,end_cus *list.List)(r *list.List){
	i:=0
	for row := end_cus.Front(); row != nil; row = row.Next()  {

		r=row.Value.(*list.List)
		if(i==index){
			return r
		}
		i++
	}
	return nil
}

func getRowOfElement(index int,end_cus *list.List)(r *list.Element){
	i:=0
	for row := end_cus.Front(); row != nil; row = row.Next()  {

		if(i==index){
			return row
		}
		i++
	}
	return nil
}


func getFloatCell(index int,row *list.List)(result float32){
	i:=0
	for cel := row.Front(); cel != nil; cel = cel.Next()  {
		if i==index{
			var v= cel.Value.(string)
			var vf,err= strconv.ParseFloat(v,32)
			if err !=nil{
				return 0
			}
			return float32(vf)
		}
		i++
	}
	fmt.Println(row," index ",index," 获取失败 ")
	return 0
}

func getStringCell(index int,row *list.List)(result string){
	i:=0
	for cel := row.Front(); cel != nil; cel = cel.Next()  {
		if i==index{
			var v= cel.Value.(string)
			return v;
		}
		i++
	}
	fmt.Println(row," index ",index," 获取失败 ")
	return ""
}

func read_month(start_name string) (customers *list.List, dirt_customers *list.List) {
	path, err := filepath.Abs(start_name)
	if err != nil {
		fmt.Println(path, " 文件路径构造错误", err)
		fmt.Println("path ", path)
		return customers, dirt_customers
	}
	var xlFile *xlsx.File

	fmt.Println("going to read file ",path)
	xlFile, err = xlsx.OpenFile(path)
	if err != nil {
		fmt.Println(path, " 文件读取错误 只支持xlsx格式文档", err)
		return customers, dirt_customers
	}

	row_num := 0
	customers = list.New()
	dirt_customers = list.New()
	var one_customer_rows = list.New()
	effect_cus_row:=0
	
	for _, sheet := range xlFile.Sheets {

		for _, row := range sheet.Rows {

			if row_num == 0 {
				start_rows_name = list.New()
					for _, cell := range row.Cells {
						_ = start_rows_name.PushBack(cell.String())		//获得列名
					}
				_ = start_rows_name.PushBack("应收金额合计")
			} else {
				var row_list = list.New()
				has_ended := false
				cell_row_num := 0
				for _, cell := range row.Cells {
					_ = row_list.PushBack(cell.String())
					fmt.Println(cell.String())
					if cell_row_num == 10  && len(cell.String()) == 0 {		//获取地11个元素，月份
						has_ended = true
					}else{

						if cell_row_num==10{					//获取地11个元素
							var month,_=strconv.ParseInt(cell.Value,10,32)
							if month>0 {
								fmt.Println(month)
								effect_cus_row++
							}
						}
					}
					cell_row_num++
				}
				one_customer_rows.PushBack(row_list)
				if has_ended {

					if effect_cus_row <=3 {						//此处验证是否为呆数据

						customers.PushBack(one_customer_rows)
					} else {

						dirt_customers.PushBack(one_customer_rows)
					}
					one_customer_rows = list.New()
					effect_cus_row=0
				}

			}
			row_num++
		}
		break
	}
	fmt.Println(dirt_customers.Len())
	return customers, dirt_customers
}

/**
*
*由于end_month客户经理和催收情况为空，因此通过月初数据辅助月末数据填充
*
*/
func write_end_month(file_name string, customers *list.List,start_customers *list.List,dirt_start_customers *list.List) {

	for one_cus := customers.Front(); one_cus != nil; one_cus = one_cus.Next()  {
		
		//每个客户
		one_cus := one_cus.Value.(*list.List)
		
		var id=getId(one_cus)
		
		var start_cus=find(id,start_customers)
		
		if start_cus==nil{
			start_cus=find(id,dirt_start_customers)
		}
		
		if start_cus!=nil {
			start_cus:=start_cus.Value.(*list.List)
			//客户经理
			var manager=getStringCell(2,start_cus.Front().Value.(*list.List))
			//催收情况
			var paySituation=getStringCell(3,start_cus.Front().Value.(*list.List))
			
			for row_ :=one_cus.Front();row_!=nil;row_=row_.Next(){
			
				//每一行数据
				row_ := row_.Value.(*list.List)
				putRowValue(2,manager,row_)
				putRowValue(3,paySituation,row_)
			}
		}
	}
	write_month(file_name,customers)
}


func write_month(file_name string, customers *list.List) {
	f, err := os.Create(file_name)
	if err != nil {
		panic(err)
	}
	defer f.Close()
	f.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM

	w := csv.NewWriter(f)
	cols := []string{}
	i := 0
	
	for element := start_rows_name.Front(); element != nil; element = element.Next() {
		cols = append(cols, toString(element.Value))
		i++
	}
	w.Write(cols)
	
	
	for one_cus := customers.Front(); one_cus != nil; one_cus = one_cus.Next()  {
		
		//每个客户
		one_cus := one_cus.Value.(*list.List)
		
		i:=0
		for row_ :=one_cus.Front();row_!=nil;row_=row_.Next(){
			cels := []string{}
			
			//每一行数据
			row_ := row_.Value.(*list.List)
			
			for cel :=row_.Front();cel!=nil;cel=cel.Next(){
				
				//每一个元素
				cels = append(cels, toString(cel.Value))
			}
			
			//最后统计应收金额合计专门写一列
			if one_cus.Len()==i+1{
				cels = append(cels, toString(getFloatCell(11,row_)))			
			}
			
			w.Write(cels)
			i++
		}
	}
	w.Flush()
}

func toString(any interface{}) string {
	switch any.(type) {
	case string:
		return any.(string)
	case int:
		return strconv.Itoa(any.(int))
	case bool:
		return strconv.FormatBool(any.(bool))
	case float32:
		return strconv.FormatFloat(float64(any.(float32)),'f','E',32)
	default:
		return any.(string)
	}
	return ""
}

